-- INSERT INTO TACACSRequests(TransactionID, Type, UserID, Status, Operation, ReturnCode,TempPWEncrypted) VALUES('TACAS2', 'User', 'CG_Z020', 'NA', 'ChangePassword', '699','xyz')
--  p_UpdateTACACSTransaction 'TACAS9','INSERT','User','CG_Z020','2016-10-23 20:44:11','ChangePassword','699','PMO','890'
--  p_UpdateTACACSTransaction 'TACAS9','UPDATE','User2','CG_Z020','2016-10-23 20:44:11','ChangePassword','699','PMO','890','YOY'
--  p_UpdateTACACSTransaction 'TACAS9','READ','User2','CG_Z020','2016-10-23 20:44:11','ChangePassword','699','PMO','890','YOY'
use METAImage
go
IF OBJECT_ID('dbo.p_UpdateTACACSTransaction') IS NOT NULL
BEGIN
 DROP PROCEDURE dbo.p_UpdateTACACSTransaction
    IF OBJECT_ID('dbo.p_UpdateTACACSTransaction') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_UpdateTACACSTransaction >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_UpdateTACACSTransaction >>>'
END
go

create proc p_UpdateTACACSTransaction
(
        @TransactionID                  varchar(50),     -- TransactionID
	@DBOperation			varchar(50),     -- DBOperation UPDATE/DELETE/INSERT/READ
        @Type				varchar(50),	 -- Type
        @UserID				varchar(50),    --  UserID
        @DateToBeProcessed              varchar(20),    --  DateToBeProcessed
        @Status				varchar(200),    -- Status
        @Operation			varchar(50),    -- Operation
        @ReturnCode			varchar(200),   -- ReturnCode
        @TempPW				varchar(30),    -- TempPW
        @TempPWEncrypted		varchar(200)   -- TempPWEncrypted
       
       
        
) as
begin
        -- declares
        declare @Msg            varchar(255)    -- last error message
        declare @ErrVal         int             -- last error number   
	declare @ProcessDate	datetime
	declare @PWEncrypted    varchar(255)
        select @ErrVal=0
        -- process
        begin tran p_UpdateTACACSTransaction
                IF @DBOperation='DELETE' 
                BEGIN     
			PRINT 'DELETE'
			DELETE from TACACSRequests where  TransactionID = @TransactionID
                
                END
                ELSE IF @DBOperation='UPDATE' 
                BEGIN
                     PRINT 'UPDATE'
		     IF @DateToBeProcessed IS NOT NULL
			BEGIN
			SELECT @ProcessDate = convert(datetime, @DateToBeProcessed,120)
			END
			OPEN SYMMETRIC KEY TacacsPW_Key_01 DECRYPTION BY CERTIFICATE TacacsPW
		     UPDATE TACACSRequests SET 
			Type=			@Type		,						
			UserID=			@UserID		,			
			DateToBeProcessed=	@ProcessDate	,      
			Status=			@Status		,		
			Operation=		@Operation	,	
			ReturnCode=		@ReturnCode	,	
			TempPW=			@TempPW		
		     WHERE TransactionID = @TransactionID
		     UPDATE TACACSRequests  SET TempPWEncrypted = ENCRYPTBYKEY(KEY_GUID('TacacsPW_Key_01'), @TempPW) where TransactionID = @TransactionID
		     CLOSE SYMMETRIC KEY TacacsPW_Key_01
                END

		ELSE IF @DBOperation='READ' 
                BEGIN
                     PRINT 'READ'
			SELECT * from TACACSRequests WHERE TransactionID = @TransactionID
                END

                ELSE 
                /* insert opertion */
                BEGIN
                       
                        PRINT 'INSERT '     
			-- need to convert Datetoprocessed field to time 
			
			IF @DateToBeProcessed IS NOT NULL
			BEGIN
			SELECT @ProcessDate = convert(datetime, @DateToBeProcessed,120)
			END
			OPEN SYMMETRIC KEY TacacsPW_Key_01 DECRYPTION BY CERTIFICATE TacacsPW
			INSERT INTO TACACSRequests(TransactionID, Type, UserID,DateToBeProcessed, Status, Operation, ReturnCode,TempPW) VALUES(@TransactionID, @Type, @UserID,@ProcessDate , @Status, @Operation, @ReturnCode,@TempPW)
			UPDATE TACACSRequests  SET TempPWEncrypted = ENCRYPTBYKEY(KEY_GUID('TacacsPW_Key_01'), @TempPW) where TransactionID = @TransactionID
			CLOSE SYMMETRIC KEY TacacsPW_Key_01

                END
		
                if @ErrVal!=0
                begin
                        rollback tran p_UpdateTACACSTransaction
                        select @ErrVal=99999,@Msg='failed :' + @DBOperation+' operation'
                        goto ErrorHandler
                end
        commit tran p_UpdateTACACSTransaction

        
        return 0 

ErrorHandler:
        raiserror @ErrVal @Msg
End

go
/*
EXEC sp_procxmode 'dbo.p_UpdateTACACSTransaction','unchained'
go
*/
IF OBJECT_ID('dbo.p_UpdateTACACSTransaction') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_UpdateTACACSTransaction >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_UpdateTACACSTransaction >>>'
go
--GRANT EXECUTE ON dbo.p_UpdateTACACSTransaction TO public
--go